package com.scm.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.scm.config.ScmConfig;
import com.scm.model.Soitem;
import com.scm.model.Somain;
import com.scm.util.DbUtil;
import com.scm.util.StringUtil;

public class SomainDao extends BaseDao<Somain>{
	private static final Logger LOGGER = Logger.getLogger(SomainDao.class);
	
	public SomainDao(Connection conn) {
		super();
		this.conn = conn;
	}
	
	public SomainDao() {
	}
	
	/**
	 * 销售月度报表
	 * @param month
	 * @return
	 * @throws SQLException 
	 */
	public Map<String, Object> report(String month) throws SQLException{
		Map<String, Object> map = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = DbUtil.getConn();
			String sql = "select a.*,(ifnull(pay,0)+ifnull(prepay,0)) totalpay from "
					+ "(select count(*) totalnum,count(paytime) paytotalnum,sum(pototal) pototal,SUBSTR(CreateTime,1,7) d from somain ) a left join "
					+ "(select sum(pototal) pay,SUBSTR(CreateTime,1,7) d from somain where PayTime is not null ) b on a.d=b.d left join "
					+ "(select sum(PrePayFee) prepay,SUBSTR(CreateTime,1,7) d from somain where PayTime is  null and PrePayTime is not null ) c "
					+ "on b.d = c.d and a.d = ?";
			LOGGER.debug("[somain_reoprt]-->"+sql);
			ps = conn.prepareStatement(sql);
			ps.setString(1, month);
			rs = ps.executeQuery();
			if(rs.next()){
				map = new HashMap<String, Object>();
				map.put("totalNum", rs.getInt("totalnum"));//采购单总数
				map.put("payTotalNum", rs.getInt("paytotalnum"));//已了结采购单数量
				map.put("pototal", rs.getInt("pototal"));//采购单总金额
				map.put("totalpay", rs.getInt("totalpay"));//已付款总金额
			}
			return map;
		}finally{
			DbUtil.close(conn,rs, ps);
		}
	}
	/**
	 * 销售 单分页显示
	 */
	@Override
	public List<Somain> selectData(Map<String, String> querymap)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Somain> somains = new ArrayList<Somain>();
		try{
			StringBuilder sql = new StringBuilder("select * ");
			Map<Integer, String> params = new HashMap<Integer, String>();
			int index = generateQueryCondition(querymap, params, sql);
			//添加分页
			sql.append(" limit ?,?");
			
			LOGGER.debug("[销售单分页查询]-->"+sql);
			ps = conn.prepareStatement(sql.toString());
			for (Integer i : params.keySet()) {
				ps.setObject(i, params.get(i));
			}
			ps.setInt(index++, Integer.parseInt(querymap.get("startNo")));
			ps.setInt(index++, Integer.parseInt(querymap.get("pageSize")));
			rs = ps.executeQuery();
			while(rs.next()){
				Somain somain = new Somain();
				somain.setSoId(rs.getLong("SoId"));//销售单编号
				somain.setCreateTime(rs.getString("createTime"));//创建时间
				somain.setCustomerName(rs.getString("name"));//客户名字
				somain.setAccount(rs.getString("account"));//创建用户
				somain.setTipFee(rs.getDouble("tipfee"));//附加费用
				somain.setPoTotal(rs.getDouble("pototal"));//销售单总价格
				somain.setProductTotal(rs.getDouble("producttotal"));//销售单产品总价格
				somain.setPayType(rs.getInt("paytype"));//付款方式
				somain.setPrePayFee(rs.getDouble("prepayfee"));//最低预付款金额
				somain.setStatus(rs.getInt("status"));//处理状态
				somain.setPayTime(rs.getString("paytime"));//操作时间
				somain.setPayUser(rs.getString("payuser"));//操作用户
				somain.setPrePayTime(rs.getString("prepaytime"));//操作时间
				somain.setPrePayUser(rs.getString("prepayuser"));//操作用户
				somains.add(somain);
			}
		}finally{
			DbUtil.close(null,rs, ps);
		}
		return somains;
	}
	/**
	 * 分页查询条件
	 */
	@Override
	public int generateQueryCondition(Map<String,String> querymap,final Map<Integer,String> params,final StringBuilder sql) {
		sql.append("from somain s,customer c where s.customercode = c.customercode  ");
		int index=1;
		
		String soId = querymap.get("soId");
		if(!StringUtil.isEmpty(soId)){
			sql.append(" and s.soid like concat('%',?,'%')");
			params.put(index++, soId);
		}
		//日期范围（开始日期，截至日期）
		String startDate = querymap.get("startDate");
		if(!StringUtil.isEmpty(startDate)){
			startDate = startDate+" 00:00:00";
			sql.append(" and s.createtime >= ?");
			params.put(index++, startDate);
		}
		String endDate = querymap.get("endDate");
		if(!StringUtil.isEmpty(endDate)){
			endDate = endDate+" 23:59:59";
			sql.append(" and s.createtime <= ?");
			params.put(index++, endDate);
		}
		//供应商选择
		String customerName = querymap.get("customerName");
		if(!StringUtil.isEmpty(customerName)){
			sql.append(" and c.name like concat('%',?,'%')");
			params.put(index++, customerName);
		}
		//付款方式
		String payType = querymap.get("payType");
		if(!StringUtil.isEmpty(payType)){
			sql.append(" and s.payType = ?");
			params.put(index++, payType);
		}
		//处理状态
		String status = querymap.get("status");
		if(!StringUtil.isEmpty(status)){
			sql.append(" and s.status = ?");
			params.put(index++, status);
		}
		return index;
	}
	/**
	 * 根据id查看采购单详情
	 * @param poid
	 * @return
	 * @throws SQLException 
	 */
	public Somain queryBySoid(Long soid) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Somain somain = null;
		
		try{
			String sql = "select m.*,i.*,p.name productName,v.name customerName from somain m,soitem i,product p,customer v where m.soid = i.soid and p.productCode=i.productCode "
					+ "and v.customerCode = m.customerCode and m.soid=?";
			conn = DbUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, soid);
			rs = ps.executeQuery();
			List<Soitem> soitems = null;
			
			while(rs.next()){
				if(somain == null){
					somain = new Somain();
					soitems = new ArrayList<Soitem>();
					somain.setSoitems(soitems);
					
					somain.setSoId(soid);//订单id
					somain.setCreateTime(rs.getString("createtime"));//创建时间
					somain.setCustomerName(rs.getString("customerName"));//客户名字
					somain.setCustomerCode(rs.getString("customerCode"));
					somain.setAccount(rs.getString("account"));//创建用户
					somain.setTipFee(rs.getDouble("tipfee"));//附加费用
					somain.setPoTotal(rs.getDouble("pototal"));//采购单总价格
					somain.setProductTotal(rs.getDouble("producttotal"));//采购单产品总价格
					somain.setPayType(rs.getInt("paytype"));//付款方式
					somain.setPrePayFee(rs.getDouble("prepayfee"));//最低预付款金额
					somain.setRemark(rs.getString("remark"));
					somain.setEndTime(rs.getString("endTime"));
					somain.setEndUser(rs.getString("endUser"));
					somain.setPayTime(rs.getString("payTime"));
					somain.setPayUser(rs.getString("payUser"));
					somain.setPrePayTime(rs.getString("prePayTime"));
					somain.setPrePayUser(rs.getString("prePayUser"));
					somain.setStockTime(rs.getString("stockTime"));//入库时间
					somain.setStockUser(rs.getString("stockUser"));//入库记录人
					somain.setStatus(rs.getInt("status"));
				}
				Soitem item = new Soitem();
				item.setProductCode(rs.getString("productCode"));
				item.setItemPrice(rs.getDouble("itemPrice"));
				item.setNum(rs.getInt("num"));
				item.setProductName(rs.getString("productName"));//产品名称
				item.setUnitName(rs.getString("unitName"));
				item.setUnitPrice(rs.getDouble("unitPrice"));
				soitems.add(item);
				LOGGER.debug("[销售单查询]-->"+soitems);
			}
			return somain;
		}finally{
			DbUtil.close(conn,rs, ps);
		}
	}
	/**
	 * 收款登记或者是尾款登记
	 * @param account
	 * @param soid
	 * @return
	 * @throws SQLException 
	 */
	public int pay(String account,long soid) throws SQLException{
		Connection conn = null;
		PreparedStatement ps =null;
		try{
			conn = DbUtil.getConn();
			String sql = "update somain set paytime = now(), payuser = ?, status = ? where soid = ? "
			+"and status = (case paytype when 1 then "+ScmConfig.RECEIVE+" when 2 then "+ScmConfig.ADD+" when 3 then "+ScmConfig.RECEIVE+" end)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, account);
			ps.setInt(2, ScmConfig.PAY);
			ps.setLong(3, soid);
			return ps.executeUpdate();
		}finally{
			DbUtil.close(conn,null, ps);
		}
	}
	/**
	 * 预付款登记
	 * @param account
	 * @param soid
	 * @return
	 * @throws SQLException 
	 */
	public int prePay(String account,long soid) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = DbUtil.getConn();
			String sql = "update soamin set paytime = now(), payuser = ?, status = ? where soid = ? "
			+"and status = (case paytype when 3 then"+ScmConfig.RECEIVE+" end)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, account);
			ps.setInt(2, ScmConfig.PREPAY);
			ps.setLong(3, soid);
			return ps.executeUpdate();
		}finally{
			DbUtil.close(conn,null, ps);
		}
	}

}
